package com.sfexpress.tools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.slf4j.Logger;

import com.sfexpress.tools.entity.CheckResult;
import com.sfexpress.tools.entity.ConsCheckResult;
import com.sfexpress.tools.entity.MysqlColumnConstInfo;
import com.sfexpress.tools.entity.MysqlColumnInfo;
import com.sfexpress.tools.entity.MysqlConnInfo;
import com.sfexpress.tools.entity.MysqlIndexInfo;
import com.sfexpress.tools.entity.MysqlTableInfo;
import com.sfexpress.tools.entity.MysqlTableConstInfo;
import com.sfexpress.tools.logger.AppLogger;

/**
 * 
 * Mysql两个版本表结构校验工具
 * 
 * @author CrazyPig
 *
 */
public class MysqlTableStructChecker {
	
	static {
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
	}
	
	private static final String FLAG_IP = "${ip}";
	private static final String FLAG_PORT = "${port}";
	private static final String FLAG_DB = "${db}";
	private static String jdbcStrTemplate = "jdbc:mysql://${ip}:${port}/${db}?characterEncoding=utf8";
	
	private static Logger logger = AppLogger.getLogger();
	
	private List<Checker> checkers;
	
	public MysqlTableStructChecker() {
		
		checkers = new ArrayList<Checker>();
		checkers.add(new MysqlTableInfoChecker()); // 负责检查表级信息
		checkers.add(new MysqlColumnInfoChecker()); // 负责检查列级信息
		checkers.add(new MysqlTableConstChecker()); // 负责检查表级约束
		checkers.add(new MysqlColumnConstChecker()); // 负责检查列级约束
		checkers.add(new MysqlIndexInfoChecker()); // 负责检查索引信息
		
	}
	
	/**
	 * 
	 * 校验灰度版本与当前生产运行版本表数据结构
	 * 
	 * @param pvConnInfo 生产版本库连接信息
	 * @param pvTable 生产版本库表名
	 * @param gvConnInfo 灰度版本库连接信息
	 * @param gvTable 灰度版本库表名
	 * @return CmptCheckResult
	 * @throws Exception
	 */
	public CheckResult check(MysqlConnInfo pvConnInfo, String pvTable, MysqlConnInfo gvConnInfo, String gvTable) throws Exception {
		
		CheckResult result = new CheckResult();
		result.setPvConnInfo(pvConnInfo);
		result.setGvConnInfo(gvConnInfo);
		result.setPvTable(pvTable);
		result.setGvTable(gvTable);
		
		logger.info("获取生产版本库[host : {}, port : {}, db : {}] 表[{}]结构信息", 
				pvConnInfo.getHost(),
				pvConnInfo.getPort(),
				pvConnInfo.getDb(),
				pvTable);
		MysqlTableInfo pvTableInfo = getAllInfo(pvConnInfo, pvTable);
		
		logger.info("获取灰度版本库[host : {}, port : {}, db : {}] 表[{}]结构信息", 
				gvConnInfo.getHost(),
				gvConnInfo.getPort(),
				gvConnInfo.getDb(),
				gvTable);
		MysqlTableInfo gvTableInfo = getAllInfo(gvConnInfo, gvTable);
		
		logger.info("开始执行两个版本库表结构一致性校验 ...");
		ConsCheckResult consCheckResult = checkConsistency(gvTableInfo, pvTableInfo); // 检查结构一致性
		logger.info("完成两个版本库表结构一致性校验!");
		
//		CmptCheckResult cmptCheckResult = checkCompatibility(gvTableInfo, pvTableInfo); // 检查兼容性
		result.setConsCheckResult(consCheckResult);
//		result.setCmptCheckResult(cmptCheckResult);
			
		return result;
	}
	
	private MysqlTableInfo getAllInfo(MysqlConnInfo connInfo, String tableName) throws SQLException {
		Connection conn = null;
		MysqlTableInfo tableInfo = new MysqlTableInfo();
		try {
			// 获取数据库连接
			conn = getConn(connInfo);
			tableInfo.setTableName(tableName);
			tableInfo.setConnInfo(connInfo);
			// 获取表信息
			getTableInfo(conn, tableInfo);
			// 获取列信息
			getColumnInfo(conn, tableInfo);
			// 获取表级别约束
			getTableLevelConst(conn, tableInfo);
			// 获取列级别约束
			getColumnLevelConst(conn, tableInfo);
			// 获取索引信息
			getIndexInfo(conn, tableInfo);
		} finally {
			DbUtils.close(conn);
		}
		return tableInfo;
	}
	
	/**
	 * 检查一致性
	 * @param gvTableInfo
	 * @param pvTableInfo
	 * @return
	 * @throws Exception 
	 */
	private ConsCheckResult checkConsistency(MysqlTableInfo gvTableInfo, MysqlTableInfo pvTableInfo) throws Exception {
		ConsCheckResult result = new ConsCheckResult();
		for(Checker checker : this.checkers) {
			checker.checkConsistency(pvTableInfo, gvTableInfo, result);
		}
		return result;
	}
	
//	/**
//	 * 检查兼容性
//	 * @param gvTableInfo
//	 * @param pvTableInfo
//	 * @return
//	 * @throws Exception
//	 */
//	private CmptCheckResult checkCompatibility(MysqlTableInfo gvTableInfo, MysqlTableInfo pvTableInfo) throws Exception {
//		CmptCheckResult result = new CmptCheckResult();
//		for(Checker checker : this.checkers) {
//			checker.checkCompatibility(pvTableInfo, gvTableInfo, result);
//		}
//		return result;
//	}
	
	private void getTableInfo(Connection conn, MysqlTableInfo tableInfo) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "SELECT "
				+ "TABLE_NAME AS tableName, "
				+ "TABLE_TYPE AS tableType,"
				+ "ENGINE AS engine"
				+ "  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
		Object[] params = new Object[]{ tableInfo.getConnInfo().getDb(), tableInfo.getTableName() };
		MysqlTableInfo _tableInfo = qr.query(conn, sql, new BeanHandler<MysqlTableInfo>(MysqlTableInfo.class), params);
		tableInfo.setTableName(_tableInfo.getTableName());
		tableInfo.setTableType(_tableInfo.getTableType());
		tableInfo.setEngine(_tableInfo.getEngine());
	}
	
	private void getColumnInfo(Connection conn, MysqlTableInfo tableInfo) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "SELECT "
				+ "COLUMN_NAME AS columnName, "
				+ "COLUMN_DEFAULT AS columnDefault,"
				+ "IS_NULLABLE AS isNullable,"
				+ "DATA_TYPE AS dataType,"
				+ "CHARACTER_MAXIMUM_LENGTH AS charMaxLen,"
				+ "NUMERIC_PRECISION AS numericPrecision,"
				+ "NUMERIC_SCALE AS numericScale,"
				+ "DATETIME_PRECISION AS datetimePrecision," 
				+ "COLUMN_TYPE AS columnType,"
				+ "COLUMN_KEY AS columnKey,"
				+ "EXTRA AS extra"
				+ "  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
		Object[] params = new Object[]{ tableInfo.getConnInfo().getDb(), tableInfo.getTableName() };
		List<MysqlColumnInfo> columnsInfo = qr.query(conn, sql, new BeanListHandler<MysqlColumnInfo>(MysqlColumnInfo.class), params);
		tableInfo.setColumnInfoList(columnsInfo);
	}
	
	private void getTableLevelConst(Connection conn, MysqlTableInfo tableInfo) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "SELECT "
				+ "CONSTRAINT_NAME AS constraintName, "
				+ "CONSTRAINT_TYPE AS constraintType"
				+ "  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
		Object[] params = new Object[]{ tableInfo.getConnInfo().getDb(), tableInfo.getTableName() };
		List<MysqlTableConstInfo> tbLevelConstInfoList = qr.query(conn, sql, new BeanListHandler<MysqlTableConstInfo>(MysqlTableConstInfo.class), params);
		tableInfo.setTbLevelConstInfoList(tbLevelConstInfoList);
	}
	
	private void getColumnLevelConst(Connection conn, MysqlTableInfo tableInfo) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "SELECT "
				+ "CONSTRAINT_NAME AS constraintName, "
				+ "COLUMN_NAME AS columnName,"
				+ "POSITION_IN_UNIQUE_CONSTRAINT AS posInUniqConst,"
				+ "REFERENCED_TABLE_SCHEMA AS refTableSchema,"
				+ "REFERENCED_TABLE_NAME AS refTableName,"
				+ "REFERENCED_COLUMN_NAME AS refColumnName"
				+ "  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
		Object[] params = new Object[]{ tableInfo.getConnInfo().getDb(), tableInfo.getTableName() };
		List<MysqlColumnConstInfo> colLevelConstInfoList = qr.query(conn, sql, new BeanListHandler<MysqlColumnConstInfo>(MysqlColumnConstInfo.class), params);
		tableInfo.setColLevelConstInfoList(colLevelConstInfoList);
	}
	
	private void getIndexInfo(Connection conn, MysqlTableInfo tableInfo) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "SELECT "
				+ "NON_UNIQUE AS nonUnique, "
				+ "INDEX_SCHEMA AS indexSchema,"
				+ "INDEX_NAME AS indexName,"
				+ "COLUMN_NAME AS columnName,"
				+ "COLLATION AS collation,"
				+ "NULLABLE AS nullable,"
				+ "INDEX_TYPE AS indexType"
				+ "  FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
		Object[] params = new Object[]{ tableInfo.getConnInfo().getDb(), tableInfo.getTableName() };
		List<MysqlIndexInfo> indexInfoList = qr.query(conn, sql, new BeanListHandler<MysqlIndexInfo>(MysqlIndexInfo.class), params);
		tableInfo.setIndexInfoList(indexInfoList);
	}
	
	
	
	private Connection getConn(MysqlConnInfo connInfo) throws SQLException {
		Connection conn = DriverManager.getConnection(
				getJdbcUrl(connInfo.getHost(), connInfo.getPort(), connInfo.getDb()), 
				connInfo.getUser(), connInfo.getPassword());
		return conn;
	}
	
	private String getJdbcUrl(String host, String port, String db) {
		String jdbcUrl = jdbcStrTemplate.replace(FLAG_IP, host)
											.replace(FLAG_PORT, port)
											.replace(FLAG_DB, db);
		return jdbcUrl;
	}
	
}
